package cn.dgut.Utils;

import cn.dgut.Entity.*;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * Druid连接池的工具类
 */
public class JDBCUtils {

    //1.定义成员变量 DataSource
    private static DataSource ds ;

    /**
     * 文件的读取，只需要读取一次即可拿到这些值。使用静态代码块
     */
    static{
        try {
            //1.加载配置文件
            Properties pro = new Properties();
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("cn/dgut/Utils/druid.properties"));
            //2.获取DataSource
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 释放资源
     * @param stmt
     * @param conn
     */
    public static void close(Statement stmt,Connection conn){
        close(null,stmt,conn);
    }

    /**
     * 释放资源
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void close(ResultSet rs , Statement stmt, Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取连接池方法
     */
    public static DataSource getDataSource(){
        return  ds;
    }

    /**
     * 查询数据数目返回行数
     * @param sql sql语句
     * @param  a 乱填就好
     */
    public int count(String sql,int a){
        List<Object> list = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int count = 0;
        try {
            //1.获取连接
            conn = getConnection();
            //开启事务
            conn.setAutoCommit(false);
            stmt = conn.createStatement();//获取执行sql对象

            //5.执行sql
            rs = stmt.executeQuery(sql);

            //6.处理结果
            //循环判断游标是否是最后一行末尾。
            while(rs.next()) {
                count++;
            }
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            close(rs,stmt,conn);
        }
        return count;
    }

    /**
     * 查询数据数目返回行数
     * @param type 查找的数据类型
     */
    public int count(String type){
        List<Object> list = new ArrayList<>();
        String sql=null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int count = 0;
        try {
            //1.获取连接
            conn = getConnection();
            //开启事务
            conn.setAutoCommit(false);
            stmt = conn.createStatement();//获取执行sql对象
            if(type.equals("User")){
                sql = "select * from User";

                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    count++;
                }
            }else if(type.equals("Teacher")){
                sql = "select * from Teacher where onJob=2 ";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    count++;
                }
            }else if(type.equals("Resume")){
                sql = "select * from Teacher where onJob!=2";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    count++;
                }
            }else if(type.equals("Student")){
                sql = "select * from Student";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    count++;
                }
            }else if(type.equals("StudentWorks")){
                sql = "select * from StudentWorks";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    count++;
                }
            }else if(type.equals("Course")){
                sql = "select * from Course";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    count++;
                }
            }else if(type.equals("Recruit")){
                sql = "select * from Recruit ";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    count++;
                }
            }else if(type.equals("Select_Course")){
                sql = "select * from Select_Course";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    count++;
                }
            }else{
                return 0;
            }
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            close(rs,stmt,conn);
        }

        return count;
    }

    /**
     * 查询数据返回javaBean的list集合
     * @param type 查找的数据类型
     */
    public List queryAllData(String type){
        List<Object> list = new ArrayList<>();
        String sql=null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            conn = getConnection();
            //开启事务
            conn.setAutoCommit(false);
            stmt = conn.createStatement();//获取执行sql对象
            if(type.equals("User")){
                sql = "select * from User";

                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    User temp = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));
                    list.add(temp);
                }
            }else if(type.equals("Teacher")){
                sql = "select * from Teacher where onJob=2 ";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Teacher temp = new Teacher(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10));
                    list.add(temp);
                }
            }else if(type.equals("Resume")){
                sql = "select * from Teacher where onJob!=2";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Teacher temp = new Teacher(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10));
                    list.add(temp);
                }
            }else if(type.equals("Student")){
                sql = "select * from Student";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Student temp = new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8));
                    list.add(temp);
                }
            }else if(type.equals("StudentWorks")){
                sql = "select * from StudentWorks";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    StudentWorks temp = new StudentWorks(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5));
                    list.add(temp);
                }
            }else if(type.equals("Course")){
                sql = "select * from Course";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Course temp = new Course(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getInt(7),rs.getString(8),rs.getInt(9));
                    list.add(temp);
                }
            }else if(type.equals("Recruit")){
                sql = "select * from Recruit ";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Recruit temp = new Recruit(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
                    list.add(temp);
                }
            }else if(type.equals("Select_Course")){
                sql = "select * from Select_Course";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Select_Course temp = new Select_Course(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4));
                    list.add(temp);
                }
            }else{
                return null;
            }
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            close(rs,stmt,conn);
        }

        return list;
    }

    /**
     * 查询指定数据返回javaBean的list集合
     * @param type 查找的数据类型
     * @param key 匹配词
     */
    public List queryAllData(String type,String key){
        List<Object> list = new ArrayList<>();
        String sql=null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            conn = getConnection();
            //开启事务
            conn.setAutoCommit(false);
            stmt = conn.createStatement();//获取执行sql对象
            if(type.equals("User")){
                sql = "select * from User where user_name like '%"+key+"%' or user_phone like '%"+key+"%' or user_email like '%"+key+"%'";

                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    User temp = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));
                    list.add(temp);
                }
            }else if(type.equals("Teacher")){
                sql = "select * from Teacher where  onJob=2 and ( teacher_name like '%"+key+"%' or teacher_phone like '%"+key+"%' or teacher_email like '%"+key+"%' or tlevel like '%"+key+"%' or tinfo like '%"+key+"%')";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Teacher temp = new Teacher(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10));
                    list.add(temp);
                }
            }else if(type.equals("Resume")){
                sql = "select * from Teacher where  onJob!=2 and ( teacher_name like '%"+key+"%' or teacher_phone like '%"+key+"%' or teacher_email like '%"+key+"%' or tlevel like '%"+key+"%' or tinfo like '%"+key+"%')";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Teacher temp = new Teacher(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10));
                    list.add(temp);
                }
            }else if(type.equals("Student")){
                sql = "select * from Student where student_name like '%"+key+"%' or student_phone like '%"+key+"%' or student_email like '%"+key+"%' or slevel like '%"+key+"%' or isVip like '%"+key+"%'";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Student temp = new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8));
                    list.add(temp);
                }
            }else if(type.equals("StudentWorks")){
                sql = "select * from StudentWorks where student_name like '%"+key+"%' or time like '%"+key+"%' or content like '%"+key+"%'";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    StudentWorks temp = new StudentWorks(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5));
                    list.add(temp);
                }
            }else if(type.equals("Course")){
                sql = "select * from Course where course_name like '%"+key+"%' or hours like '%"+key+"%' or type like '%"+key+"%' or course_begin like '%"+key+"%' or cinfo like '%"+key+"%'";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Course temp = new Course(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getInt(7),rs.getString(8),rs.getInt(9));
                    list.add(temp);
                }
            }else if(type.equals("Recruit")){
                sql = "select * from Recruit where title like '%"+key+"%' or phone like '%"+key+"%' or rinfo like '%"+key+"%'";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Recruit temp = new Recruit(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
                    list.add(temp);
                }
            }else if(type.equals("Select_Course")){
                sql = "select * from Select_Course where course_id like '%"+key+"%' or sid like '%"+key+"%' or time like '%"+key+"%'";
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Select_Course temp = new Select_Course(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4));
                    list.add(temp);
                }
            }else{
                return null;
            }
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            close(rs,stmt,conn);
        }

        return list;
    }

    /**
     * 查询数据返回javaBean的list集合  -- 分页功能的实现
     * @param type 查找的数据类型
     * @param showPage 展示的第几页
     * @param pageSize 每页的数据数
     */
    public List queryAllData(String type,int showPage,int pageSize){
        int startSize = (showPage - 1) * pageSize;
        List<Object> list = new ArrayList<>();
        String sql=null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            conn = getConnection();
            //开启事务
            conn.setAutoCommit(false);
            stmt = conn.createStatement();//获取执行sql对象
            if(type.equals("User")){
                sql = "select * from User limit "+startSize+","+pageSize;

                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    User temp = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));
                    list.add(temp);
                }
            }else if(type.equals("Teacher")){
                sql = "select * from Teacher where onJob=2 limit "+startSize+","+pageSize;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Teacher temp = new Teacher(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10));
                    list.add(temp);
                }
            }else if(type.equals("Resume")){
                sql = "select * from Teacher where onJob!=2 limit "+startSize+","+pageSize;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Teacher temp = new Teacher(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10));
                    list.add(temp);
                }
            }else if(type.equals("Student")){
                sql = "select * from Student limit "+startSize+","+pageSize;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Student temp = new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8));
                    list.add(temp);
                }
            }else if(type.equals("StudentWorks")){
                sql = "select * from StudentWorks limit "+startSize+","+pageSize;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    StudentWorks temp = new StudentWorks(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5));
                    list.add(temp);
                }
            }else if(type.equals("Course")){
                sql = "select * from Course limit "+startSize+","+pageSize;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Course temp = new Course(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getInt(7),rs.getString(8),rs.getInt(9));
                    list.add(temp);
                }
            }else if(type.equals("Recruit")){
                sql = "select * from Recruit limit "+startSize+","+pageSize;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Recruit temp = new Recruit(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
                    list.add(temp);
                }
            }else if(type.equals("Select_Course")){
                sql = "select * from Select_Course limit "+startSize+","+pageSize;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                while(rs.next()) {
                    Select_Course temp = new Select_Course(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4));
                    list.add(temp);
                }
            }else{
                return null;
            }
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            close(rs,stmt,conn);
        }

        return list;
    }

    /**
     * 查询某条数据返回javaBean
     * @param type 查找的数据类型
     * @param id id
     */
    public Object queryOneData(String type,int id)  {
        Object data=null;
        String sql=null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            conn = getConnection();
            //开启事务
            conn.setAutoCommit(false);
            stmt = conn.createStatement();//获取执行sql对象
            if(type.equals("User")){
                sql = "select * from User where user_id = "+id;

                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                rs.next();
                User temp = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));
                data = temp;
            }else if(type.equals("Teacher")){
                sql = "select * from Teacher where onJob=2 and teacher_id = "+id;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                rs.next();
                Teacher teacher = new Teacher(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10));
                data = teacher;

            }else if(type.equals("Resume")){
                sql = "select * from Teacher where onJob!=2 and teacher_id = "+id;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                rs.next();
                Teacher teacher = new Teacher(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10));
                data = teacher;
            }else if(type.equals("Student")){
                sql = "select * from Student where student_id = "+id;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                rs.next();
                Student temp = new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8));
                data = temp;

            }else if(type.equals("StudentWorks")){
                sql = "select * from StudentWorks where StudentWorks_id="+id;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                rs.next();
                StudentWorks temp = new StudentWorks(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5));
                data = temp;
            }else if(type.equals("Course")){
                sql = "select * from Course where course_id = "+id;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                rs.next();
                Course temp = new Course(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getInt(7),rs.getString(8),rs.getInt(9));
                data = temp;

            }else if(type.equals("Recruit")){
                sql = "select * from Recruit where recruit_id = "+id;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                rs.next();
                Recruit temp = new Recruit(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
                data = temp;

            }else if(type.equals("Select_Course")){
                sql = "select * from Select_Course where id = "+id;
                //5.执行sql
                rs = stmt.executeQuery(sql);

                //6.处理结果
                //循环判断游标是否是最后一行末尾。
                rs.next();
                Select_Course temp = new Select_Course(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4));
                data = temp;

            }else{
                return null;
            }
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            close(rs,stmt,conn);
        }

        return data;
    }

    /**
     * 执行UPDATE、INSERT、DELETE类的sql语句
     * @param sql sql语句
     */
    public int querySQL(String sql){
        Connection conn = null;
        Statement stmt = null;
        int status=0;
        try {
            conn = getConnection();
            //开启事务
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            //5.执行sql
            status = stmt.executeUpdate(sql);

            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            close(stmt,conn);
        }
        return status;
    }

}
